#!/usr/bin/python
# coding=utf8

from lib import pymysql


class MySQLHelper(object):
    def __init__(self, conn_dict):
        self.__conn = self.get_conn(conn_dict)

    # 尽量使用封装好的函数执行sql，否则，记得关闭资源和处理异常
    @staticmethod
    def get_conn(conn_dict):
        return pymysql.connect(**conn_dict)

    # 从数据库读取数据,返回格式tuple
    @staticmethod
    def get_data(connection, sql, data=None):
        with MySQLHelper(connection) as db:
            return db.execute_select(sql, data)

    # update到数据库
    @staticmethod
    def update_data(connection, sql, data_dict=None):
        with MySQLHelper(connection) as db:
            db.execute_update(sql, data_dict)

    def execute(self, sql_str):
        if sql_str is None or len(sql_str) == 0:
            raise Exception("参数不能为空：sql_str")

        self.__cur.execute(sql_str)
        data = self.__cur.fetchall()
        self.__conn.commit()
        return data

    # 插入数据，返回数据主键
    def execute_insert(self, sql_str, args=None):
        if sql_str is None or len(sql_str) == 0:
            raise Exception("参数不能为空：insert_str")

        self.__cur.execute(sql_str, args)
        last_id = self.__conn.insert_id()
        self.__conn.commit()
        return last_id

    # 更新数据，返回更新条数
    def execute_update(self, sql_str, args=None):
        if sql_str is None or len(sql_str) == 0:
            raise Exception("参数不能为空：update_str")

        count = self.__cur.execute(sql_str, args)
        self.__conn.commit()
        return count

    # 批量插入数据
    def execute_batch_insert(self, sql_str, args=None):
        if sql_str is None or len(sql_str) == 0:
            raise Exception("参数不能为空：batch_insert_str")

        self.__cur.executemany(sql_str, args)
        self.__conn.commit()


    # 执行带参数的查询，返回查询结果
    def execute_select(self, sql_str, args=None):
        if sql_str is None or len(sql_str) == 0:
            raise Exception("参数不能为空：select_str")

        self.__cur.execute(sql_str, args)
        data = self.__cur.fetchall()
        #self.__conn.commit()
        return data

    # 执行带参数的删除
    def execute_delete(self, sql_str, args=None):
        if sql_str is None or len(sql_str) == 0:
            raise Exception("参数不能为空：select_str")

        self.__cur.execute(sql_str, args)
        data = self.__cur.fetchall()
        self.__conn.commit()
        return data

    def __enter__(self):
        # 打开游标
        self.__cur = self.__conn.cursor()
        return self

    def __exit__(self, type, value, tb):
        # sql执行异常回滚
        if type is not None:
            self.__conn.rollback()
            print("MySQL execption: type : %s, value : %s, trace : %s" % (type, value, tb))

        # 关闭资源
        if self.__cur is not None:
            self.__cur.close()
        if self.__conn is not None:
            self.__conn.close()
